
using System;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;

namespace Common
{
	/// <summary>
	/// Description of PatientExaminationDao.
	/// </summary>
	public class PatientExaminationDao
	{
		private PatientExaminationDao()
		{
		}
		
		public static int insert(PatientExamination pe)
		{
            string sql = "insert into PatientExamination(mstr_id,examination_id,type,result)values(?,?,?,?);";
			OleDbParameter[] parameters = new OleDbParameter[4];
            parameters[0] = new OleDbParameter("@mstr_id", OleDbType.Integer);
            parameters[0].Value = pe.MstrId;
            parameters[1] = new OleDbParameter("@symptom_id", OleDbType.Integer);
            parameters[1].Value = pe.ExaminationId;
            parameters[2] = new OleDbParameter("@type", OleDbType.VarChar);
            parameters[2].Value = pe.Type;
            parameters[3] = new OleDbParameter("@result", OleDbType.VarChar);
            parameters[3].Value = pe.Result;
			
			return AccessDBUtil.ExecuteInsert(sql,parameters);
		}

        public static int update(PatientExamination pe)
		{
            string sql = "update PatientExamination set examination_id=?,result=? where id=?";
			OleDbParameter[] parameters = new OleDbParameter[3];
            parameters[0] = new OleDbParameter("@symptom_id", OleDbType.Integer);
            parameters[0].Value = pe.ExaminationId;
            parameters[1] = new OleDbParameter("@result", OleDbType.VarChar);
            parameters[1].Value = pe.Result;
			parameters[2] = new OleDbParameter("@id",OleDbType.Integer);
			parameters[2].Value=pe.Id;
			
			return AccessDBUtil.ExecuteNonQuery(sql,parameters);
			
			
		}
        public static PatientExamination getById(int id)
		{
            //to do
            string sql = "select pe.*,e.type as item_type,e.item from PatientExamination pe inner join Examination e on pe.examination_id = e.id where pe.id=? order by pe.id";
			OleDbParameter[] parameters = new OleDbParameter[1];
			parameters[0] = new OleDbParameter("@id",OleDbType.Integer);
			parameters[0].Value=id;
			DataSet ds = AccessDBUtil.ExecuteQuery(sql,parameters);
            if (ds.Tables["ds"].Rows.Count == 0) return null;
			return Row2PatientExamination(ds.Tables["ds"].Rows[0]);
		}

        public static int getCountByExaminationId(int mstr_id, int examination_id,string type)
        {

            string sql = "select * from PatientExamination where mstr_id = ? and examination_id = ? and type = ?";
            OleDbParameter[] parameters = new OleDbParameter[3];
            parameters[0] = new OleDbParameter("@mstr_id", OleDbType.Integer);
            parameters[0].Value = mstr_id;
            parameters[1] = new OleDbParameter("@symptom_id", OleDbType.Integer);
            parameters[1].Value = examination_id;
            parameters[2] = new OleDbParameter("@type", OleDbType.VarChar);
            parameters[2].Value = type;

            DataSet ds = AccessDBUtil.ExecuteQuery(sql, parameters);

            return ds.Tables["ds"].Rows.Count;

        }

        public static int deleteById(int id)
        {
            string sql = "delete from PatientExamination where id=?";
            OleDbParameter[] parameters = new OleDbParameter[1];
            parameters[0] = new OleDbParameter("@id", OleDbType.Integer);
            parameters[0].Value = id;
            return AccessDBUtil.ExecuteNonQuery(sql, parameters);
        }

        private static PatientExamination Row2PatientExamination(DataRow row)
		{
            PatientExamination pe = new PatientExamination();
            pe.Id = Int32.Parse(row["id"].ToString());
            pe.MstrId = Int32.Parse(row["mstr_id"].ToString());
            pe.ExaminationId = Int32.Parse(row["examination_id"].ToString());
            pe.Type = row["type"].ToString();
            pe.Result = row["result"].ToString();
            pe.ExaminationType = row["item_type"].ToString();
            pe.ExaminationItem = row["item"].ToString();
            return pe;
		}

        public static List<PatientExamination> getAllPatientExaminations(int mstr_id,string type)
        {
            string sql = "select pe.*,e.type as item_type,e.item as item from PatientExamination pe inner join Examination e on pe.examination_id = e.id where pe.mstr_id=? and pe.type=? order by pe.id";
            OleDbParameter[] parameters = new OleDbParameter[2];
            parameters[0] = new OleDbParameter("@mstr_id", OleDbType.Integer);
            parameters[0].Value = mstr_id;
            parameters[1] = new OleDbParameter("@type", OleDbType.VarChar);
            parameters[1].Value = type;
            DataSet ds = AccessDBUtil.ExecuteQuery(sql, parameters);

            List<PatientExamination> pes = new List<PatientExamination>();
            for (int i = 0; i < ds.Tables["ds"].Rows.Count; i++)
            {
                pes.Add(Row2PatientExamination(ds.Tables["ds"].Rows[i]));
            }
            return pes;
        }

        public static int getAllPatientExaminationsCount(int mstr_id, string type)
        {
            string sql = "select count(1) as examination_count from PatientExamination pe inner join Examination e on pe.examination_id = e.id where pe.mstr_id=? and pe.type=?";
            OleDbParameter[] parameters = new OleDbParameter[2];
            parameters[0] = new OleDbParameter("@mstr_id", OleDbType.Integer);
            parameters[0].Value = mstr_id;
            parameters[1] = new OleDbParameter("@type", OleDbType.VarChar);
            parameters[1].Value = type;
            DataSet ds = AccessDBUtil.ExecuteQuery(sql, parameters);

            int count = 0;
            if (ds.Tables["ds"].Rows.Count == 1)
            {
                DataRow row = ds.Tables["ds"].Rows[0];
                count = Int32.Parse(row["examination_count"].ToString());
            }

         
            return count;
        }
		
	}
}
